import time
from datetime import datetime, timedelta
from decimal import Decimal

import mysql.connector
from openpyxl import load_workbook

import config


################################################################################################################
# PROCEDURES:
# STEP 1: get all 'new' offline meter files
# STEP 2: for each new files, iterate all rows and read cell's value and store data to energy data list
# STEP 3: insert or update energy data to table offline meter hourly in energy database
# STEP 4: update file status to 'done' or 'error'
################################################################################################################


def calculate_hourly(logger):
    while True:
        # the outermost while loop to reconnect server if there is a connection error
        ################################################################################################################
        # STEP 1: get all 'new' offline meter files
        ################################################################################################################
        cnx = None
        cursor = None
        try:
            cnx = mysql.connector.connect(**config.myems_historical_db)
            cursor = cnx.cursor()
        except Exception as e:
            logger.error("Error in step 1.1 of offline meter.calculate_hourly " + str(e))
            if cursor:
                cursor.close()
            if cnx:
                cnx.close()
            # sleep several minutes and continue the outer loop to reconnect the database
            print("Could not connect the MyEMS Historical Database, and go to sleep 60 seconds...")
            time.sleep(60)
            continue

        print("Connected to MyEMS Historical Database")

        print("Getting all new offline meter files")
        try:
            query = (" SELECT id, file_name, file_object "
                     " FROM tbl_offline_meter_files "
                     " WHERE status = 'new' "
                     " ORDER BY id ")

            cursor.execute(query, )
            rows_files = cursor.fetchall()
        except Exception as e:
            logger.error("Error in step 1.2 of offline meter.calculate_hourly " + str(e))
            time.sleep(60)
            continue
        finally:
            if cursor:
                cursor.close()
            if cnx:
                cnx.close()

        excel_file_list = list()
        if rows_files is not None and len(rows_files) > 0:
            for row_file in rows_files:
                excel_file_list.append({"id": row_file[0],
                                        "name": row_file[1],
                                        "file_object": row_file[2]})
        else:
            print("there isn't any new files found, and go to sleep 60 seconds...")
            time.sleep(60)
            continue

        ################################################################################################################
        # STEP 2: for each new files, dump file object to local file and then load workbook from the local file
        ################################################################################################################
        for excel_file in excel_file_list:
            print("read data from offline meter file" + excel_file['name'])
            is_valid_file = True
            fw = None
            try:
                fw = open("myems-normalization.blob", 'wb')
                fw.write(excel_file['file_object'])
                fw.close()
            except Exception as e:
                logger.error("Error in step 2.1 of offline meter.calculate_hourly " + str(e))
                if fw:
                    fw.close()
                # mark as invalid file
                is_valid_file = False

            fr = None
            wb = None
            try:
                fr = open("myems-normalization.blob", 'rb')
                wb = load_workbook(fr, data_only=True)
                fr.close()
            except Exception as e:
                logger.error("Error in step 2.2 of offline meter.calculate_hourly " + str(e))
                if fr:
                    fr.close()
                # mark as invalid file
                is_valid_file = False

            energy_data_list = list()
            # grab the active worksheet

            if is_valid_file:
                ws = wb.active

                # get timezone offset in minutes, this value will be returned to client
                timezone_offset = int(config.utc_offset[1:3]) * 60 + int(config.utc_offset[4:6])
                if config.utc_offset[0] == '-':
                    timezone_offset = -timezone_offset

                for row in ws.iter_rows(min_row=3, max_row=1024, min_col=1, max_col=34):
                    offline_meter_data = dict()
                    offline_meter_data['offline_meter_id'] = None
                    offline_meter_data['offline_meter_name'] = None
                    offline_meter_data['data'] = dict()
                    col_num = 0

                    for cell in row:
                        col_num += 1
                        print(cell.value)
                        if col_num == 1:
                            # get offline meter ID
                            if cell.value is not None:
                                offline_meter_data['offline_meter_id'] = cell.value
                            else:
                                break
                        elif col_num == 2:
                            # get offline meter name
                            if cell.value is None:
                                break
                            else:
                                offline_meter_data['offline_meter_name'] = cell.value
                        elif col_num > 3:
                            # get date of the cell
                            try:
                                start_datetime_local = datetime(year=ws['A2'].value,
                                                                month=ws['B2'].value,
                                                                day=col_num - 3)
                            except ValueError:
                                # invalid date and go to next cell in this row until reach max_col
                                continue

                            start_datetime_utc = start_datetime_local - timedelta(minutes=timezone_offset)

                            if cell.value is None:
                                # if the cell is empty then stop at that day
                                break
                            else:
                                offline_meter_data['data'][start_datetime_utc] = Decimal(cell.value)

                    if len(offline_meter_data['data']) > 0:
                        print("offline_meter_data:" + str(offline_meter_data))
                        energy_data_list.append(offline_meter_data)

            ############################################################################################################
            # STEP 3: insert or update energy data to table offline meter hourly in energy database
            ############################################################################################################
            print("to valid offline meter id in excel file...")
            if len(energy_data_list) == 0:
                print("Could not find any offline meters in the file...")
                print("and go to process the next file...")
                is_valid_file = False
            else:
                try:
                    cnx = mysql.connector.connect(**config.myems_system_db)
                    cursor = cnx.cursor()
                except Exception as e:
                    logger.error("Error in step 3.1 of offlinemeter.calculate_hourly " + str(e))
                    if cursor:
                        cursor.close()
                    if cnx:
                        cnx.close()
                    time.sleep(60)
                    continue

                try:
                    cursor.execute(" SELECT id, name, hourly_low_limit, hourly_high_limit"
                                   " FROM tbl_offline_meters ")
                    rows_offline_meters = cursor.fetchall()
                except Exception as e:
                    logger.error("Error in step 3.2 of offlinemeter.calculate_hourly " + str(e))
                    time.sleep(60)
                    continue
                finally:
                    if cursor:
                        cursor.close()
                    if cnx:
                        cnx.close()

                if rows_offline_meters is None or len(rows_offline_meters) == 0:
                    print("Could not find any offline meters in the MyEMS System Database...")
                    time.sleep(60)
                    continue
                else:
                    offline_meter_id_set = set()
                    for row_offline_meter in rows_offline_meters:
                        # valid offline meter id in excel file
                        offline_meter_id_set.add(row_offline_meter[0])

                    for energy_data_item in energy_data_list:
                        if energy_data_item['offline_meter_id'] not in offline_meter_id_set:
                            is_valid_file = False
                            break

                        for row_offline_meter in rows_offline_meters:
                            if row_offline_meter[0] == energy_data_item['offline_meter_id']:
                                for key in energy_data_item['data']:
                                    if row_offline_meter[2] > (energy_data_item['data'][key]/24):
                                        is_valid_file = False
                                        break
                                    elif row_offline_meter[3] < (energy_data_item['data'][key]/24):
                                        is_valid_file = False
                                        break
                                break

                if is_valid_file:
                    ####################################################################################################
                    # delete possibly exists offline meter hourly data in myems energy database,
                    # and then insert new offline meter hourly data
                    ####################################################################################################
                    try:
                        cnx = mysql.connector.connect(**config.myems_energy_db)
                        cursor = cnx.cursor()
                    except Exception as e:
                        logger.error("Error in step 3.2 of offlinemeter.calculate_hourly " + str(e))
                        if cursor:
                            cursor.close()
                        if cnx:
                            cnx.close()
                        time.sleep(60)
                        continue

                    try:
                        for energy_data_item in energy_data_list:
                            offline_meter_id = energy_data_item['offline_meter_id']
                            print(energy_data_item['data'].items())
                            for start_datetime_utc, daily_value in energy_data_item['data'].items():
                                end_datetime_utc = start_datetime_utc + timedelta(hours=24)
                                actual_value = \
                                    daily_value / (Decimal(24) * Decimal(60) / Decimal(config.minutes_to_count))
                                cursor.execute(" DELETE FROM tbl_offline_meter_hourly "
                                               " WHERE offline_meter_id = %s "
                                               "       AND start_datetime_utc >= %s "
                                               "       AND start_datetime_utc < %s ",
                                               (offline_meter_id,
                                                start_datetime_utc.isoformat()[0:19],
                                                end_datetime_utc.isoformat()[0:19]))
                                cnx.commit()
                                # todo: check with hourly low limit and hourly high limit
                                add_values = (" INSERT INTO tbl_offline_meter_hourly "
                                              "             (offline_meter_id, start_datetime_utc, actual_value) "
                                              " VALUES  ")

                                while start_datetime_utc < end_datetime_utc:
                                    add_values += " (" + str(offline_meter_id) + ","
                                    add_values += "'" + start_datetime_utc.isoformat()[0:19] + "',"
                                    add_values += str(actual_value) + "), "
                                    start_datetime_utc += timedelta(minutes=config.minutes_to_count)

                                print("add_values:" + add_values)
                                # trim ", " at the end of string and then execute
                                cursor.execute(add_values[:-2])
                                cnx.commit()
                    except Exception as e:
                        logger.error("Error in step 3.3 of offlinemeter.calculate_hourly " + str(e))
                        time.sleep(60)
                        continue
                    finally:
                        if cursor:
                            cursor.close()
                        if cnx:
                            cnx.close()

            ############################################################################################################
            # STEP 4: update file status to 'done' or 'error'
            ############################################################################################################
            print("to update offline meter file status to done...")
            try:
                cnx = mysql.connector.connect(**config.myems_historical_db)
                cursor = cnx.cursor()
            except Exception as e:
                logger.error("Error in step 4.1 of offlinemeter.calculate_hourly " + str(e))
                if cursor:
                    cursor.close()
                if cnx:
                    cnx.close()
                time.sleep(60)
                continue

            try:
                update_row = (" UPDATE tbl_offline_meter_files "
                              " SET status = %s "
                              " WHERE id = %s ")
                cursor.execute(update_row, ('done' if is_valid_file else 'error', excel_file['id'],))
                cnx.commit()
            except Exception as e:
                logger.error("Error in step 4.2 of offlinemeter.calculate_hourly " + str(e))
                time.sleep(60)
                continue
            finally:
                if cursor:
                    cursor.close()
                if cnx:
                    cnx.close()

        # end of for excel_file in excel_file_list

        print("go to sleep")
        time.sleep(300)
        print("wake from sleep, and go to work")
    # end of the outermost while
